package model;

import java.sql.*;

import com.mysql.jdbc.PreparedStatement;

import myBeans.Comment;

public class CommentRespository {

	Connection conn;

	public CommentRespository(Connection conn) {
		this.conn = conn;
	}

	public boolean pageShareWithMeComment(Comment input) {
		try {
			/*
			 * java.sql.PreparedStatement insertComment = null;
			 * 
			 * String sql =
			 * "INSERT INTO sharewithme.tbshare(sharegroupid, sharetypeid, " +
			 * "sharevalue, sharecomment, userid, isbn, sharedate) VALUES (?,?,?,?,?,?,NOW())"
			 * ;
			 * 
			 * conn.setAutoCommit(false); insertComment =
			 * conn.prepareStatement(sql); insertComment.setString(1,
			 * input.getSharegroupid()); insertComment.setInt(2,
			 * input.getSharetypeid()); insertComment.setInt(3,
			 * input.getSharevalue()); insertComment.setString(4,
			 * input.getSharecomment()); insertComment.setString(5,
			 * input.getUserid()); insertComment.setString(6, input.getIsbn());
			 * insertComment.execute(); conn.commit();
			 */

			String sql = "INSERT INTO sharewithme.tbshare(sharegroupid, sharetypeid, "
					+ "sharevalue, sharecomment, userid, isbn, sharedate) "
					+ "VALUES ('"
					+ input.getSharegroupid()
					+ "',"
					+ input.getSharetypeid()
					+ ", "
					+ input.getSharevalue()
					+ ", '"
					+ input.getSharecomment()
					+ "','"
					+ input.getUserid()
					+ "' "
					+ ",'"
					+ input.getIsbn()
					+ "',NOW())";
			
			// *** increase comment
			String sql1 = "UPDATE ShareWithMe.TBTopView "
					+ " SET countcomment = countcomment + 1 "
					+ "where ISBN = '" + input.getIsbn() + "'";

			Statement stmt = conn.createStatement();
			if (stmt.executeUpdate(sql) > 0 && stmt.executeUpdate(sql1) > 0) {
				return true;
			}
		} catch (SQLException ex) {
			System.out
					.println("Exception CommentRespository.pageShareWithMeComment : "
							+ ex.getMessage());
			ex.printStackTrace();
		}

		return false;
	}

	public String GetShareGroupIdFrompageCall(String pageCallName) {
		try {

			String sql = "select menuid, pagecall from tbmenu where pagecall = '"
					+ pageCallName + "'";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				return rs.getString("menuid");
			}

		} catch (SQLException ex) {
			System.out
					.println("Exception CommentRespository.GetShareGroupIdFrompageCall : "
							+ ex.getMessage());
			ex.printStackTrace();
		}
		return "0";
	}

	public boolean likeWorm(Comment input) {
		try {
			java.sql.PreparedStatement insert = null;

			String sql = "INSERT INTO sharewithme.tbshare (ShareGroupID, ShareTypeID"
					+ ", ShareValue, UserID, ISBN, sharedate) "
					+ "VALUES (?, ?, ?, ?, ?, NOW())";

			conn.setAutoCommit(false);
			insert = conn.prepareStatement(sql);
			insert.setString(1, input.getSharegroupid());
			insert.setInt(2, input.getSharetypeid());
			insert.setInt(3, input.getSharevalue());
			insert.setString(4, input.getUserid());
			insert.setString(5, input.getIsbn());
			insert.execute();
			conn.commit();
			return true;
		} catch (SQLException e) {
			System.out.println("SQLException CommentRespository.likeworm : "
					+ e.getMessage());
			e.printStackTrace();
		}
		return false;
	}

	public boolean checkLikeAllowed(Comment input) {

		try {
			String sql = "select * FROM sharewithme.tbshare "
					+ "where sharegroupid = '" + input.getSharegroupid() + "' "
					+ "and sharetypeid = " + input.getSharetypeid() + " "
					+ "and sharevalue = " + input.getSharevalue() + " "
					+ "and userid = '" + input.getUserid() + "' and isbn = '"
					+ input.getIsbn() + "'";

			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return false;
			}
		} catch (SQLException e) {
			System.out
					.println("SQLException CommentRespository.checkLikeAllowed : "
							+ e.getMessage());
			e.printStackTrace();
		}
		return true;
	}

	public boolean giveARate(Comment input) {
		try {
			// *** tbshare vote
			String sql = "INSERT INTO sharewithme.tbshare "
					+ "(ShareGroupID, ShareTypeID, ShareValue, UserID, ISBN) "
					+ "VALUES ('" + input.getSharegroupid() + "', "
					+ input.getSharetypeid() + ", " + input.getSharevalue()
					+ ", '" + input.getUserid() + "', " + "'" + input.getIsbn()
					+ "')";

			// *** increase vote
			String sql1 = "UPDATE ShareWithMe.TBTopView "
					+ " SET countrate = countrate + 1 " + "where ISBN = '"
					+ input.getIsbn() + "'";

			Statement stmt = conn.createStatement();

			if ((stmt.executeUpdate(sql) > 0) && (stmt.executeUpdate(sql1) > 0)) {
				return true;
			}

		} catch (SQLException e) {
			System.out.println("SQLException CommentRespository.getARate : "
					+ e.getMessage());
			e.printStackTrace();
		}
		return false;
	}
}
